/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.hy.solr.config;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.util.StringUtils;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.*;

/**
 * @author hy
 */
public class ExeclUtil {

    public static List<Map<String, Object>> XLSReader(InputStream file, List<String> columns) throws IOException, InvalidFormatException {
        List<Map<String, Object>> data = new ArrayList<>();
        //创建Workbook对象，poi对Excel操作
        Workbook book = null;
        book = WorkbookFactory.create(file);
        Sheet sheet = book.getSheetAt(0);
        Iterator<Row> rows = sheet.rowIterator();
        int rownum = 0;
        Row firstdRow;
        List<String> coordinateList = new ArrayList<>();
        while (rows.hasNext()) {
            //从第一行读取表头
            if (rownum == 0) {
                firstdRow = rows.next();
                Iterator<Cell> secondCells = firstdRow.cellIterator();
                while (secondCells.hasNext()) {
                    Cell secondcell = secondCells.next();
                    String value = secondcell.getStringCellValue();
                    coordinateList.add(value);
                }
                rownum++;
            } else {
                Row row = rows.next();
                if (row.cellIterator() != null) {
                    Map<String, Object> m = new HashMap<>();
                    int columnum = 0;
                    for (String column : columns) {
                        if (coordinateList.contains(column)) {
                            String key = column;
                            String value = "";
                            if (!StringUtils.isEmpty(row.getCell(columnum))) {
                                switch (row.getCell(columnum).getCellType()) {
                                    case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                                        //如果为时间格式的内容
                                        value = new DecimalFormat("0").format(row.getCell(columnum).getNumericCellValue());
                                        break;
                                    case HSSFCell.CELL_TYPE_STRING: // 字符串
                                        value = row.getCell(columnum).getStringCellValue();
                                        break;
                                    case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                                        value = row.getCell(columnum).getBooleanCellValue() + "";
                                        break;
                                    case HSSFCell.CELL_TYPE_FORMULA: // 公式
//                                        value = row.getCell(columnum).getCellFormula() + "";
                                        try {
                                            value = row.getCell(columnum).getStringCellValue();
                                        } catch (IllegalStateException e) {
                                            value = String.valueOf(row.getCell(columnum).getNumericCellValue());
                                        }
                                        break;
                                    case HSSFCell.CELL_TYPE_BLANK: // 空值
                                        value = "";
                                        break;
                                    case HSSFCell.CELL_TYPE_ERROR: // 故障
                                        value = "非法字符";
                                        break;
                                    default:
                                        value = "未知类型";
                                        break;
                                }
                            }
                            if (!StringUtils.isEmpty(value))
                                m.put(key, value);
                            columnum++;
                        }
                    }
                    if (!m.isEmpty())
                        data.add(m);
                    rownum++;
                } else {
                    break;
                }
            }
        }
        return data;
    }

    public static XSSFWorkbook createXMlModel(List<String> tableColumns, List<Map<String, Object>> data, String steetName) throws IOException {
        XSSFWorkbook book = new XSSFWorkbook();
        XSSFSheet sheet = book.createSheet(steetName);
        //设置一个字段的宽度，默认是10 * 256
        sheet.setColumnWidth(0, 10 * 256);
        //设置默认宽度、高度值
        sheet.setDefaultColumnWidth(20);
        sheet.setDefaultRowHeightInPoints(20);
        XSSFCellStyle cs = book.createCellStyle();
        XSSFCellStyle cs2 = book.createCellStyle();
        XSSFDataFormat df = book.createDataFormat();
        // create 2 fonts objects
        XSSFFont f = book.createFont();
        f.setFontHeightInPoints((short) 12);
        f.setColor(IndexedColors.RED.getIndex());
        XSSFFont f2 = book.createFont();
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.RED.getIndex());
        // Set cell style and formatting
        cs.setFont(f);
        //cs.setDataFormat(df.getFormat("#,##0.0"));
        // Set the other cell style and formatting
        cs2.setBorderBottom(BorderStyle.THIN);
        cs2.setDataFormat(df.getFormat("text"));
        cs2.setFont(f2);
        int headCellnum = 0;
        int rownum = 0;
        XSSFRow hrComment = sheet.createRow(rownum++);
        for (String colum : tableColumns) {
            int column = headCellnum++;
            hrComment.createCell(column).setCellValue(colum);
        }
        for (Map<String, Object> m : data) {
            XSSFRow dataRow = sheet.createRow(rownum++);
            headCellnum = 0;
            for (String colum : tableColumns) {
                if (m.containsKey(colum)) {
                    Object o = m.get(colum);
                    if (o instanceof Float){
                        dataRow.createCell(headCellnum).setCellValue(o==null?0:Integer.valueOf(String.valueOf(o)));
                    }else if (o instanceof Double){
                        dataRow.createCell(headCellnum).setCellValue(o==null?0:Integer.valueOf(String.valueOf(o)));
                    }else{
                        dataRow.createCell(headCellnum).setCellValue(o==null?"":String.valueOf(o).replaceAll("null", ""));
                    }
                } else {
                    dataRow.createCell(headCellnum).setCellValue("");
                }
                headCellnum++;
            }
        }
        return book;
    }
}